package Server.DAO;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import Server.domain.User;

public class UserDAO {
	
	private static String registeUserSql="insert into " +
			"user (user_password,user_gender,user_age,user_money,user_mail,user_phoneNumber,user_question,user_answer,user_name)"+
			"values(?,?,?,?,?,?,?,?,?)";

	private static String deleteUserSql="delete from user where user_name = ?";
	
	private static String updateUserSql="update user set user_password=?," +
			"user_gender=?,user_age=?,user_money=?,user_mail=?,user_phoneNumber=?," +
			"user_question=?,user_answer=? where user_name = ?";
	
	private static String getUserByUsernameAndPasswordSql="select * from user where user_name=? and user_password=?";
	private static String getUserByUsernameSql="select * from user where user_name=?";
	private static String updateUserMoneyByUserId="update user set user_money=? where user_id =?";
	
    public void insert(User user){
    	
    	Connection connection = DBUtil.getConnection();
    	try{
    		PreparedStatement pStatement = connection.prepareStatement(registeUserSql);
    		setUserToPreparedStatement(pStatement,user);
    		int result = pStatement.executeUpdate();
    		if(result == 1){
    			pStatement.close();
    		}
    		DBUtil.closePreparedStatement(pStatement);
			DBUtil.closeConnection(connection);
    	} catch (Exception e){
    		e.printStackTrace();
    	}
    	
    }

    public void delete(String username){
    	Connection connection = DBUtil.getConnection();
    	try{
    		PreparedStatement pStatement = connection.prepareStatement(deleteUserSql);
    		pStatement.setString(1, username);
    	    pStatement.executeUpdate();
    		DBUtil.closePreparedStatement(pStatement);
    		DBUtil.closeConnection(connection);
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    }

    public void update(User user){
    	Connection connection = DBUtil.getConnection();
    	try {
			PreparedStatement pStatement = connection.prepareStatement(updateUserSql);
			setUserToPreparedStatement(pStatement,user);
		    pStatement.executeUpdate();
			DBUtil.closePreparedStatement(pStatement);
			DBUtil.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    }
    
    public User getUserByUsernameAndPassword(String username,String password){
    	User selectUser = null;
    	Connection connection = DBUtil.getConnection();
    	try {
			PreparedStatement pStatement = connection.prepareStatement(getUserByUsernameAndPasswordSql);
			pStatement.setString(1, username);
			pStatement.setString(2, password);
			ResultSet resultSet = pStatement.executeQuery();
			if(resultSet.next()){
				selectUser = getUserFromResultSet(resultSet);
			}
			DBUtil.closeResultSet(resultSet);
			DBUtil.closePreparedStatement(pStatement);
			DBUtil.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return selectUser;
    }
    
    public boolean getUserByUsername(String username){
    	boolean isExist = false;
    	Connection connection = DBUtil.getConnection();
		try {
			PreparedStatement pStatement = connection.prepareStatement(getUserByUsernameSql);
			pStatement.setString(1, username);
			ResultSet resultSet = pStatement.executeQuery();
	    	if(resultSet.next()){
	    		isExist = true;
	    	}
	    	DBUtil.closeResultSet(resultSet);
	    	DBUtil.closePreparedStatement(pStatement);
	    	DBUtil.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return isExist;
    }
    
    public void updateUserMoneyByUserId(int userId,double userMoney){
    	Connection connection = DBUtil.getConnection();
    	try {
			PreparedStatement pStatement = connection.prepareStatement(updateUserMoneyByUserId);
			pStatement.setDouble(1,userMoney);
			pStatement.setInt(2, userId);
			pStatement.executeUpdate();
			DBUtil.closePreparedStatement(pStatement);
			DBUtil.closeConnection(connection);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	
    	
    }
    
    private void setUserToPreparedStatement(PreparedStatement pStatement,User user) throws SQLException{
    	
    	pStatement.setString(1,user.getPassword());
    	pStatement.setString(2,user.getGender());
    	pStatement.setInt(3,user.getAge());
    	pStatement.setDouble(4,user.getMoney());
    	pStatement.setString(5,user.getMail());
    	pStatement.setString(6,user.getPhoneNumber());
    	pStatement.setString(7,user.getQuestion());
    	pStatement.setString(8,user.getAnswer());
    	pStatement.setString(9,user.getUsername());
    }
    
    private User getUserFromResultSet(ResultSet resultSet) throws SQLException{
    	User user = new User();
    	user.setUserId(resultSet.getInt(1));
    	user.setUsername(resultSet.getString(2));
    	user.setPassword(resultSet.getString(3));
    	user.setGender(resultSet.getString(4));   
    	user.setAge(resultSet.getInt(5));  
    	user.setMoney(resultSet.getDouble(6));   
    	user.setMail(resultSet.getString(7));   
    	user.setPhoneNumber(resultSet.getString(8));   
    	user.setQuestion(resultSet.getString(9));   
    	user.setAnswer(resultSet.getString(10));  
    	return user;
    }
    
   /*public static void main(String[] args){
    	UserDAO dao = new UserDAO();
    	//User user = new User("123","女",20,2000,"986283@mail","13675432123","hello?","Kite","Shirley");
    	//dao.insert(user);
    	
    	//User user2 = new User("123","女",20,2000,"345261@mail","13975432123","who?","Shirley","Shirley");
    	//dao.update(user2);
    
    	//User user3 = dao.getUserByUsernameAndPassword("Amy","123");
    	//System.out.print(user3.toString());
    	
    	//boolean flag = dao.getUserByUsername("Shirley");
    	//System.out.print(flag);
    	
    	//dao.delete("Shirley");
    	
    	//dao.updateUserMoneyByUserId(2,3500);
    }*/
}
